/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Metodos;


import Atributos.Disciplina;
import Atributos.HorasAula;
import Atributos.Professor;
import Atributos.Serie;
import BD.CriaConexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andre
 */
public class HorasAulaMet {
    private Connection conexao;
        public HorasAulaMet(){
        try {
            this.conexao=(Connection) CriaConexao.getConexao();
        } catch (SQLException ex) {
            Logger.getLogger(HorasAulaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
       
        }

    

    public int BuscarHoraAulaPelaDisciplina(Serie s, Professor p, Disciplina d) {
        String sql = "select hora_aula.horas_aula from hora_aula,professor,disciplina,serie,descricao where hora_aula.id_prof_ha=professor.id_prof and serie.id_turma_s=descricao.id_desc and hora_aula.id_disc_ha=disciplina.id_disc and hora_aula.id_serie_turma_ha=serie.id_serie and serie.nome_serie like ? and descricao.descricao_desc like ? and professor.nome_prof like ? and disciplina.nome_disc like ?";
            int id = 0;
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, s.getNome());
                stmt.setString(2, s.getTurma());
                stmt.setString(3, p.getNome());
                stmt.setString(4, d.getNome());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                       
                    id=rs.getInt("horas_aula");  
                        
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    public void EditaHA(int id_serie, int id_prof, int id_disc, int HA) throws SQLException {
        String sql = " update hora_aula set horas_aula=?  where hora_aula.id_serie_turma_ha=? and hora_aula.id_prof_ha=? and hora_aula.id_disc_ha=?";
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setInt(1, HA);
            stmt.setInt(2, id_serie);
            stmt.setInt(3, id_prof);
            stmt.setInt(4, id_disc);
            
            stmt.execute();
        }
    }



    public void Adicionar(int id_serie, int id_prof, int id_disc, int HA) throws SQLException {
        String sql2 = "select * from hora_aula where hora_aula.id_serie_turma_ha=? and hora_aula.id_prof_ha=? and hora_aula.id_disc_ha=?";
            int id = -1;
            int ha2=-1;
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql2)) {
                stmt.setInt(1, id_serie);
                stmt.setInt(2, id_prof);
                stmt.setInt(3, id_disc);
                
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                       
                    id=rs.getInt("id_ha");  
                    ha2=rs.getInt("horas_aula");    
                        
                    }
                }
            }
        
        if(id==-1){
             String sql = "insert into hora_aula (id_serie_turma_ha,id_prof_ha,id_disc_ha,horas_aula)"+" values (?,?,?,?)";
        PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql);
            consulta.setInt(1, id_serie);
            consulta.setInt(2, id_prof);
            consulta.setInt(3, id_disc);
            consulta.setInt(4, HA);
            consulta.execute();
        
        }else{
          String sql3 = " update hora_aula set horas_aula=? where id_ha=?";
        PreparedStatement stmt2;
        
            stmt2 = (PreparedStatement) conexao.prepareStatement(sql3);
             stmt2.setInt(1, ha2+HA);
             stmt2.setInt(2, id);
            stmt2.execute();
            stmt2.close();
       
        }
        
        
        
        
        
       
    }

    public List<HorasAula> BuscarPorProf(String nome) throws SQLException {
         String sql = "select hora_aula.id_ha,serie.nome_serie,descricao.descricao_desc,professor.nome_prof,disciplina.nome_disc,hora_aula.horas_aula "
                + "from hora_aula,serie,descricao,professor,disciplina "
                + "where hora_aula.id_serie_turma_ha=serie.id_serie and serie.id_turma_s=descricao.id_desc and hora_aula.id_prof_ha=professor.id_prof and hora_aula.id_disc_ha=disciplina.id_disc and professor.nome_prof like ? order by serie.nome_serie,descricao.descricao_desc";
        List<HorasAula> minhaLista;
        try (java.sql.PreparedStatement stmt = conexao.prepareStatement(sql)) {
            stmt.setString(1, nome);
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    HorasAula a1 = new HorasAula();
                    a1.setId(rs.getInt("id_ha"));
                    a1.setSerie(rs.getString("nome_serie"));
                    a1.setTurma(rs.getString("descricao_desc"));
                    a1.setProfessor(rs.getString("nome_prof"));
                    a1.setMateria(rs.getString("nome_disc"));
                    a1.setHoras_aula(rs.getInt("horas_aula"));
                    
                    minhaLista.add(a1);
                }
            }
        }
        return minhaLista;
    }

    public List<HorasAula> BuscarPorMat(String nome) throws SQLException {
         String sql = "select serie.nome_serie,descricao.descricao_desc,professor.nome_prof,disciplina.nome_disc,hora_aula.id_ha,hora_aula.horas_aula "
                + "from serie,professor,disciplina,hora_aula,descricao "
                + "where hora_aula.id_serie_turma_ha=serie.id_serie and serie.id_turma_s=descricao.id_desc and hora_aula.id_prof_ha=professor.id_prof and hora_aula.id_disc_ha=disciplina.id_disc and disciplina.nome_disc like ? order by serie.nome_serie,descricao.descricao_desc";
        List<HorasAula> minhaLista;
        try (java.sql.PreparedStatement stmt = conexao.prepareStatement(sql)) {
            stmt.setString(1, nome);
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    HorasAula a1 = new HorasAula();
                    a1.setId(rs.getInt("id_ha"));
                    a1.setSerie(rs.getString("nome_serie"));
                    a1.setTurma(rs.getString("descricao_desc"));
                    a1.setProfessor(rs.getString("nome_prof"));
                    a1.setMateria(rs.getString("nome_disc"));
                    a1.setHoras_aula(rs.getInt("horas_aula"));
                    
                    minhaLista.add(a1);
                }
            }
        }
        return minhaLista;
    }

    public void Excluir(int id) throws SQLException {
          String sql_e = "delete from hora_aula where id_ha=?";
        try (java.sql.PreparedStatement consulta_e = conexao.prepareStatement(sql_e)) {
            consulta_e.setLong(1, id);
            consulta_e.execute();
        }
    }

    public List<Professor> BuscaHAporTurma(String serie, String turma) throws SQLException {
            String sql = "select  professor.nome_prof, disciplina.nome_disc, hora_aula.horas_aula from professor, disciplina, hora_aula,serie, descricao where hora_aula.id_prof_ha=professor.id_prof and hora_aula.id_disc_ha=disciplina.id_disc and hora_aula.id_serie_turma_ha=serie.id_serie and serie.id_turma_s=descricao.id_desc and serie.nome_serie like ? and descricao.descricao_desc like ? order by professor.nome_prof, disciplina.nome_disc";
        List<Professor> minhaLista;
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, serie);
            stmt.setString(2, turma);
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    Professor a1 = new Professor();
                    
                    a1.setNome(rs.getString("nome_prof"));
                    a1.setDisciplinas(rs.getString("nome_disc"));
                    a1.setAulas(rs.getInt("horas_aula"));
                    minhaLista.add(a1);
                }
            }
        }
        return minhaLista;
    }

    public int BuscaQuantHA(String serie, String turma) {
        String sql = "select sum(hora_aula.horas_aula) from hora_aula, serie,descricao where hora_aula.id_serie_turma_ha=serie.id_serie and serie.id_turma_s=descricao.id_desc and serie.nome_serie like ? and descricao.descricao_desc like ?";
        
            int soma = 0;
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, serie);
                stmt.setString(2, turma);
               
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                       
                    soma=rs.getInt("sum");  
                        
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return soma;
    }

    

   

   
        
   
}
